import csv
import codecs
import logging
import datetime
from utils.db.mysql_ops import MysqlPool
from utils.db.oracle_ops import OraclePool
from django.shortcuts import render
from django.http import JsonResponse, FileResponse
from dbmanager.models import DBConfig, DBLog
from assets.models import environment_choice
from django.contrib.auth.models import Group
from users.models import UserProfile
import re
from utils.crypt_pwd import CryptPwd
from django.contrib.auth.decorators import permission_required
from utils.decorators import permission_alternate_required
from django.core.paginator import Paginator
from django.db.models import Q
import json
from ast import literal_eval


VALID_PRIVS = {'CREATE': '允许创建数据库和表', 'DROP': '允许删除数据库，表和视图', 'GRANT OPTION': '允许对账户赋权和取消权限',
               'LOCK TABLES': '允许对具有SELECT权限的表进行锁表操作', 'REFERENCES': '允许创建外键', 'EVENT': '允许使用事件调度器',
               'ALTER': '允许修改表', 'DELETE': '允许使用DELETE', 'INDEX': '允许创建和删除索引', 'INSERT': '允许使用INSERT',
               'SELECT': '允许使用SELECT', 'UPDATE': '允许使用UPDATE', 'CREATE TEMPORARY TABLES': '允许创建临时表',
               'TRIGGER': '允许触发器相关操作', 'CREATE VIEW': '允许创建和修改视图', 'SHOW VIEW': '允许使用 SHOW CREATE VIEW',
               'ALTER ROUTINE': '允许修改和删除存储过程', 'CREATE ROUTINE': '允许创建存储过程', 'EXECUTE': '允许执行存储过程',
               'FILE': '允许执行创建文件和读取文件相关操作', 'CREATE TABLESPACE': '允许创建、修改和删除表空间和日志文件组',
               'CREATE USER': '允许创建、删除、重命名用户和取消用户所有权限', 'PROCESS': '允许使用SHOW PROCESSLIST',
               'RELOAD': '允许使用FLUSH', 'REPLICATION CLIENT': '允许查看master和slave',
               'REPLICATION SLAVE': '允许读取从master复制过来的binlog文件', 'SHOW DATABASES': '允许使用SHOW DATABASES',
               'SHUTDOWN': '允许停止mysql服务', 'SUPER': '允许执行管理员命令，比如：CHANGE MASTER、KILL、PURGE BINARY LOGS、SET GLOBAL、debug',
               'ALL PRIVILEGES': '允许所有权限', 'USAGE': '没有任何权限，只允许登录'}

# 'PROXY': '允许用户代理(将用户权限代理给某个用户)

@permission_required('dbmanager.view_dbconfig', raise_exception=True)
def db_list(request):
    if request.method == 'GET':
        dbs = DBConfig.objects.select_related('db_server').all()
        groups = Group.objects.all()
        users = UserProfile.objects.all()
        db_type_ = DBConfig.db_type_
        db_oracle_type_ = DBConfig.db_oracle_type_
        environment_choice_ = environment_choice
        return render(request, 'dbmanager/db_list.html', locals())


@permission_alternate_required(['dbmanager.add_dbconfig', 'dbmanager.view_db_password'], raise_exception=True)
def search_db_password(request, pk):
    if request.method == 'GET':
        dbconfig = DBConfig.objects.get(id=pk)
        results = {'db_password': CryptPwd().decrypt_pwd(dbconfig.db_password)}
        return JsonResponse({'code': 200, 'results': results})


def find_dbconfig(request):
    if request.method == 'GET':
        search = request.GET.get('search')
        page = request.GET.get('page')
        offset = request.GET.get('offset')
        db_type = request.GET.get('db_type')
        assets = DBConfig.objects.filter(Q(db_server__asset_management_ip__IP__contains=search) | Q(db_server__asset_nu__icontains=search) | Q(db_user__icontains=search) | Q(db_name__icontains=search),db_type=db_type)\
            .values_list('id', 'db_server__asset_nu','db_server__asset_management_ip__IP','db_user','db_name')
        group = Paginator(assets, offset).page(page)
        results = [{'id': i[0], 'text': i[1] + '|' + i[2] + '|' + i[3] + '|' + i[4]} for i in group.object_list]
        return JsonResponse(
            {'code': 200, 'results': results, 'page': group.paginator.num_pages, 'total_count': group.paginator.count})


@permission_required('dbmanager.handle_db', raise_exception=True)
def db_exec(request):
    if request.method == 'GET':
        return render(request, 'dbmanager/db_exec.html', locals())
    elif request.method == 'POST':
        pk = request.POST.get('pk')
        sql = request.POST.get('sql', '')
        sql_type = request.POST.get('sql_type')
        sql_file = request.FILES.get('upload_file')
        if sql_file:
            try:
                try:
                    sql = ''.join({chunk.decode('utf-8') for chunk in sql_file.chunks(chunk_size=1024)})
                except:
                    sql = ''.join({chunk.decode('gbk') for chunk in sql_file.chunks(chunk_size=1024)})
            except UnicodeDecodeError:
                return JsonResponse({'code': 500, 'msg': '只支持gbk和utf8编码的文件！'})
            finally:
                sql_file.close()
            return JsonResponse({'code': 200, 'data': sql, 'msg': 'sql上传成功！'})

        db = DBConfig.objects.select_related('db_server').get(id=pk)
        dbs = gen_db_users(request.user)
        if db not in dbs:
            return JsonResponse({'code': 500, 'msg': '你的账户不存在连接的所属用户组中！您无权执行相关操作！'})
        try:
            if sql_type == 'sql-many':
                try:
                    sqls = sql.split('args=')
                    run_sql = sqls[0].rstrip()
                    args = literal_eval(sqls[1])
                    db_num = len(args) or 1
                except:
                    return JsonResponse({'code': 500, 'msg': '输入格式有误，请检查！'})
            else:
                sqls = sql_split(sql)
                db_num = len(sqls) or 1
            if db.db_type == '0':
                conn = MysqlPool(db.db_server.asset_management_ip.IP, db.db_port, db.db_user,
                                 CryptPwd().decrypt_pwd(db.db_password), db.db_name, max_pool_size=db_num, charset=db.db_code or 'utf-8')
            else:
                if db.db_oracle_type == '1':
                    conn = OraclePool(None, None, db.db_user, CryptPwd().decrypt_pwd(db.db_password), None, max_pool_size=db_num, charset=db.db_code or 'utf-8', dsn=db.db_name)
                else:
                    conn = OraclePool(db.db_server.asset_management_ip.IP, db.db_port, db.db_user,
                             CryptPwd().decrypt_pwd(db.db_password), db.db_name, max_pool_size=db_num, charset=db.db_code or 'utf-8')
            if sql == 'show tables':
                res = conn.get_tables()
                return JsonResponse({'code': 200, 'data': res, 'msg': 'sql执行成功！'})
            elif sql_type == 'select':
                count = 1
                db_sql_res = []
                for sql in sqls:
                    table_heads, res = conn.exec_select(sql)
                    if isinstance(table_heads, list):
                        db_log_id = sql_log(db_config=db, db_login_user=request.user, db_sql_content=sql, db_sql_res=res, db_sql_res_thead=str(table_heads))
                        db_sql_res.append({'table_heads': table_heads, 'data': res, 'msg': '第{}条语句执行结果：查询成功。'.format(count), 'db_log_id': db_log_id})
                    else:
                        sql_log(db_config=db, db_login_user=request.user, db_sql_content=sql, db_sql_res=db_sql_res)
                        if not res:
                            db_sql_res.append({'data': '第{}条语句执行结果：执行成功。'.format(count)})
                        else:
                            db_sql_res.append({'data': '第{}条语句 {} 执行失败，提示：{}'.format(count,sql,str(res))})
                            break
                    count += 1
                return JsonResponse({'code': 200, 'data': db_sql_res})
            elif sql_type == 'sql-one':
                count = 1
                db_sql_res=[]
                for sql in sqls:
                    flag = True
                    res = conn.exec_sql_one(sql)
                    if isinstance(res, int):
                        db_sql_res.append('第{}条语句执行结果：受影响的行数: {}。'.format(count,res))
                    elif isinstance(res, str):
                        db_sql_res.append('第{}条语句 {} 执行失败，提示：{}'.format(count,sql,str(res)))
                        flag = False
                    else:
                        db_sql_res.append('第{}条语句执行结果：执行成功。'.format(count))
                    sql_log(db_config=db, db_login_user=request.user, db_sql_content=sql, db_sql_res=db_sql_res)
                    if flag:
                        count += 1
                    else:
                        break
                msg = ';'.join(db_sql_res)
                return JsonResponse(
                    {'code': 200, 'data': db_sql_res, 'msg': msg})
            elif sql_type == 'sql-many':
                res = conn.exec_sql_many(run_sql, args)
                db_log_id = sql_log(db_config=db, db_login_user=request.user, db_sql_content=sql,
                                    db_sql_res='受影响的行数: {}'.format(res))
                return JsonResponse(
                    {'code': 200, 'data': '受影响的行数: {}'.format(res), 'db_log_id': db_log_id, 'msg': 'sql执行成功！'})
        except Exception as e:
            return JsonResponse({'code': 500, 'data': None, 'msg': 'sql执行失败！{}'.format(e)})

def sql_split(sqls):
    unformat_sql = re.split('(?:;[ ]*\n)',sqls)
    format_sql = []
    for unformat in unformat_sql:
        unformat= unformat.strip()
        if len(unformat) == 0 or re.compile('^\s+$').match(unformat):
            continue
        else:
            if re.compile('.+;$').match(unformat):
                unformat = unformat[0:-1]
            format_sql.append(unformat)
    return format_sql


def sql_log(db_config, db_login_user, db_sql_content, db_sql_res, db_sql_res_thead=''):
    try:
        log = DBLog.objects.create(
            db_config=db_config,
            db_login_user=db_login_user,
            db_sql_content=db_sql_content,
            db_sql_res=db_sql_res,
            db_sql_res_thead=db_sql_res_thead
        )
        return log.id
    except Exception as e:
        logging.getLogger().error('添加SQL操作记录失败：{}'.format(e))


@permission_required('dbmanager.view_dblog', raise_exception=True)
def db_log(request):
    if request.method == 'GET':
        db_logs = DBLog.objects.select_related('db_config').all() if request.user.is_superuser else \
            DBLog.objects.select_related('db_config').filter(db_login_user=request.user)
        return render(request, 'dbmanager/db_log.html', locals())
    elif request.method == 'POST':
        start_time = request.POST.get('startTime')
        end_time = request.POST.get('endTime')
        new_end_time = datetime.datetime.strptime(end_time, '%Y-%m-%d') + datetime.timedelta(1)
        end_time = new_end_time.strftime('%Y-%m-%d')
        try:
            records = []
            search_records = DBLog.objects.select_related('db_config').filter(db_run_datetime__gt=start_time,
                                                                              db_run_datetime__lt=end_time)
            for search_record in search_records:
                record = {
                    'id': search_record.id,
                    'db_project': '{}|{}'.format(search_record.db_config.db_server.project.project_name,
                                                 search_record.db_config.db_server.project.get_project_env_display()),
                    'db_server': '{}|{}'.format(search_record.db_config.db_server.asset_management_ip.IP,
                                                search_record.db_config.db_name),
                    'db_sql_content': search_record.db_sql_content,
                    'db_sql_res': search_record.db_sql_res,
                    'db_sql_res_thead': search_record.db_sql_res_thead,
                    'db_login_user': search_record.db_login_user.username,
                    'db_run_datetime': search_record.db_run_datetime,
                }
                records.append(record)
            return JsonResponse({'code': 200, 'records': records})
        except Exception as e:
            return JsonResponse({'code': 500, 'error': '查询失败：{}'.format(e)})


@permission_required(['dbmanager.view_dblog', 'dbmanager.handle_db'], raise_exception=True)
def db_log_detail(request):
    pk = request.GET.get('pk') if request.GET.get('pk') else request.POST.get('pk')
    log = DBLog.objects.get(id=pk)
    res = literal_eval(log.db_sql_res)
    data = [list(i) for i in res]
    heads = literal_eval(log.db_sql_res_thead)

    if request.method == 'POST':
        return JsonResponse({'code': 200, 'heads': heads, 'data': data})
    elif request.method == 'GET':
        filename = 'query_res.csv'

        try:
            with codecs.open(filename, 'w+', 'utf_8_sig') as csvfile:
                writer = csv.writer(csvfile)
                writer.writerow(heads)
                writer.writerows(data)

            response = FileResponse(open(filename, 'rb'))
            response['Content-Type'] = 'application/octet-stream'
            response['Content-Disposition'] = 'attachment;filename="{filename}"'.format(filename=filename)
            return response
        except Exception as e:
            logging.getLogger().error('导出sql查询结果失败：{}'.format(e))


def gen_db_users(user):
    """
    生成当前用户下的所有可用的用户列表
    :param user: 当前用户，request.user
    """
    groups = user.groups.all()

    db_server_users = DBConfig.objects.prefetch_related('db_group').all()

    db_users = {i for i in DBConfig.objects.prefetch_related('db_belong_user').filter(db_belong_user=user)}

    for db_server_user in db_server_users:
        if not set(db_server_user.db_group.all()).isdisjoint(set(groups)):  # 判断db_user的组与当前用户的组是否有交集
            db_users.add(db_server_user)

    users = db_server_users if user.is_superuser else db_users
    return users


@permission_required('dbmanager.change_dbconfig', raise_exception=True)
def batch_change_db_belong_user(request):
    if request.method == 'POST':
        data = json.loads(request.body)
        type = data.get('type', 'update')
        db_belong_user = data.get('db_belong_user', False)
        db_id = data.get('db_id', False)
        if db_belong_user and db_id:
            dbconfig = DBConfig.objects.get(id=db_id)
            if type == 'add':
                dbconfig.db_belong_user.remove(*db_belong_user)
                dbconfig.db_belong_user.add(*db_belong_user)
                dbconfig.save()
            elif type == 'update':
                dbconfig.db_belong_user.clear()
                dbconfig.db_belong_user.add(*db_belong_user)
                dbconfig.save()
            elif type == 'delete':
                dbconfig.db_belong_user.remove(*db_belong_user)
                dbconfig.save()
            return JsonResponse({'code': 200 })
        return JsonResponse({'code': 500 })


@permission_required('dbmanager.change_dbconfig', raise_exception=True)
def batch_change_db_group(request):
    if request.method == 'POST':
        data = json.loads(request.body)
        type = data.get('type', 'update')
        db_group = data.get('db_group', False)
        db_id = data.get('db_id', False)
        if db_group and db_id:
            dbconfig = DBConfig.objects.get(id=db_id)
            if type == 'add':
                dbconfig.db_group.remove(*db_group)
                dbconfig.db_group.add(*db_group)
                dbconfig.save()
            elif type == 'update':
                dbconfig.db_group.clear()
                dbconfig.db_group.add(*db_group)
                dbconfig.save()
            elif type == 'delete':
                dbconfig.db_group.remove(*db_group)
                dbconfig.save()
            return JsonResponse({'code': 200 })
        return JsonResponse({'code': 500 })
